package com.yjd.bi.etl.sql;

import com.yjd.bi.comm.utils.DbNameUtils;

public class BusinessSqlConstants {
	public static final String GET_MAX_INVEST_TATISTIC_DATE = "select MAX(statistical_date) from " + DbNameUtils.get("yjd_invest_info") ;
	//成交额，投资项目，按照实际投标的时间
	public final static String GET_INVEST_PRICE = "select ifnull(sum(price),0) from " + DbNameUtils.get("yjd_invest") + " where addtime between ? and ?";
	//成交笔数， 投资项目，按照实际满标时间
	public final static String GET_INVEST_AMOUNT = "select ifnull(count(*),0) from " + DbNameUtils.get("yjd_borrow") + " where status >= 50 and endtime between ? and ?";
	//待回收金额（不含代偿余额）－投资项目
	public final static String GET_RECOVER_PRICE="SELECT ifnull(SUM(b.capital),0.00) FROM" + DbNameUtils.get("yjd_borrow") + "  a," + DbNameUtils.get("yjd_arrears_form") + " b WHERE a.borrow_id = b.borrow_id AND a.repay_starttime<= ? AND ( ( b.advance = 1 AND b.advancetime> ?) OR ( b.advance = 0 AND b.status = 1 AND b.uptime>?) OR ( b.status = 0 AND b.advance = 0) )";
	//待回收金额（含代偿余额）－投资项目
	public final static String GET_RECOVER_PRICE1 ="SELECT ifnull(SUM(b.capital), 0.00) FROM yjd_borrow a, yjd_arrears_form b WHERE a.borrow_id = b.borrow_id AND a.repay_starttime <= ? AND ( b.status = 0 OR ( b.status = 1 AND b.uptime> ?))";
	//yjd_invest_info  insert sql
	public final static String INVESTINFO_INSERTSQL = "insert into " + DbNameUtils.get("yjd_invest_info") + "(invest_price,invest_amount,recover_price,recover_price1,statistical_date,uptime) VALUES (?,?,?,?,?,unix_timestamp())";

	
	//债权转让成交额
	public final static String GET_TRANSFER_PRICE  = "SELECT ifnull(sum(price),0) FROM " + DbNameUtils.get("yjd_creditor_transfer") + " ct WHERE oktime BETWEEN ? AND ? AND  `status` = 3";
	//债权转让成交笔数
	public final static String GET_TRANSFER_AMOUNT = "SELECT ifnull(count(*),0) FROM " + DbNameUtils.get("yjd_creditor_transfer") + " ct WHERE oktime BETWEEN ? AND ? AND  `status` = 3";
	//insert sql
	public final static String TRANSFER_INSERTSQL = "insert into " + DbNameUtils.get("yjd_creditor_transfer_info") + "(transfer_price,transfer_amount,statistical_date,uptime) VALUES (?,?,?,unix_timestamp())";
	public static final String GET_MAX_TRANSFER_TATISTIC_DATE = "select MAX(statistical_date) from " + DbNameUtils.get("yjd_creditor_transfer_info");
}
